package wsepForum.persistance;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Vector;

import org.apache.commons.lang.StringEscapeUtils;

import wsepForum.common.elements.*;
import wsepForum.common.elements.AForumElement.ForumElementType;
import wsepForum.common.elements.User.UserType;
import wsepForum.logging.ForumLogger;
import wsepForum.logging.ForumLogger.LoggerCategory;
import wsepForum.persistance.exceptions.*;

public class SQLConnector implements IDBConnector {


	private Connection con;
	private String DBName;

	public SQLConnector() {
		this("wsepForum");
	}

	public SQLConnector(String dbName) {
		con = null;
		DBName = dbName;
	}


	@Override
	public void close() {
		try {
			if(con != null) {
				ForumLogger.getInstance().info("Closing MySQL Connection...", LoggerCategory.PERSISTANCE);
				con.close();
			}
		} catch (Exception e) {

		}
	}


	@Override
	public boolean initDB() throws DataBaseIOException {
		try {
			ForumLogger.getInstance().info("Initializing MySQL DB...", LoggerCategory.PERSISTANCE);
		    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+DBName, "wsep", "forum");
		    //con = DriverManager.getConnection("jdbc:mysql://84.109.159.201:3306/wsepForum", "wsep", "forum");

		    String sql;

		    Statement statement = con.createStatement();

		    sql = "CREATE TABLE IF NOT EXISTS Users " +
    			  "(id VARCHAR(20) NOT NULL PRIMARY KEY, " +
    			  "password TEXT NOT NULL, " +
    		      "salt INT NOT NULL, " +
    		      "type VARCHAR(10) NOT NULL) ENGINE=InnoDB;";
		    statement.addBatch(sql);


		    sql = "CREATE TABLE IF NOT EXISTS Sections " +
    		"(id INT NOT NULL PRIMARY KEY, " +
    		"subject TEXT NOT NULL, " +
    		"time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
    		"pid INT NOT NULL, " +
    		"CONSTRAINT FOREIGN KEY(pid) REFERENCES Sections(id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;";
		    statement.addBatch(sql);

		    sql = "CREATE TABLE IF NOT EXISTS Topics " +
			"(id INT NOT NULL PRIMARY KEY, " +
			"subject TEXT NOT NULL, " +
    		"time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
			"pid INT NOT NULL, " +
			"CONSTRAINT FOREIGN KEY(pid) REFERENCES Sections(id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;";
		    statement.addBatch(sql);

		    sql = "CREATE TABLE IF NOT EXISTS Messages " +
    		"(id INT NOT NULL PRIMARY KEY, " +
    		"subject TEXT NOT NULL, " +
    		"author VARCHAR(20), " +
    		"body TEXT NOT NULL, " +
    		"time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
    		"pid INT NOT NULL, " +
    		"CONSTRAINT FOREIGN KEY(author) REFERENCES Users(id) ON DELETE SET NULL ON UPDATE CASCADE, " +
    		"CONSTRAINT FOREIGN KEY(pid) REFERENCES Topics(id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;";
		    statement.addBatch(sql);

		    sql = "CREATE TABLE IF NOT EXISTS SectionAdmins " +
			"(section_id INT NOT NULL, " +
			"user_id VARCHAR(20) NOT NULL, " +
			"CONSTRAINT FOREIGN KEY(section_id) REFERENCES Sections(id) ON DELETE CASCADE ON UPDATE CASCADE, " +
			"CONSTRAINT FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE ON UPDATE CASCADE, " +
			"CONSTRAINT PRIMARY KEY (section_id,user_id)) ENGINE=InnoDB;";
		    statement.addBatch(sql);

		    sql = "CREATE TABLE IF NOT EXISTS GeneralId " +
		    "(general_id INT NOT NULL PRIMARY KEY DEFAULT 1) ENGINE=InnoDB;";
		    statement.addBatch(sql);

		    sql = "CREATE TABLE IF NOT EXISTS LoginStats " +
			"(id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
			"user_id VARCHAR(20), " +
    		"login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, " +
    		"logout_time TIMESTAMP DEFAULT 0, " +
			"CONSTRAINT FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;";
		    statement.addBatch(sql);

			User admin = new User("admin", "admin", UserType.ADMIN);
			String passwordToEncrypt = admin.getPassword() + admin.getUserSalt().toString();
			String encryptedPassword = MD5Encoder.EncryptString(passwordToEncrypt);
			admin.setPassword(encryptedPassword);

		    sql = "INSERT IGNORE INTO Users (id, password, salt, type) VALUES ('" +
		    admin.getUserName() + "', '" +
		    admin.getPassword() + "', '" +
		    admin.getUserSalt() + "', '" +
		    admin.getUserType() + "');";
		    statement.addBatch(sql);

		    sql = "SET FOREIGN_KEY_CHECKS = 0;";
		    statement.addBatch(sql);

		    sql = "INSERT IGNORE INTO Sections (id, subject, pid) " +
		    	  "VALUES ('0', 'Main', '-1');";
		    statement.addBatch(sql);


		    sql = "SET FOREIGN_KEY_CHECKS = 1;";
		    statement.addBatch(sql);

		    statement.executeBatch();

		    ResultSet set = statement.executeQuery("SELECT * FROM GeneralId;");
		    if (set.next() != false) {
		    	DataHandler.elementGeneralID = set.getInt("general_id");
		    } else {
			    statement.execute("INSERT IGNORE INTO GeneralId (general_id) VALUES(DEFAULT);");
		    }
	    	set.close();
		    statement.close();


		} catch (Exception e) {
			close();
			throw new DataBaseIOException("Cannot connect to MySQL : " + e.getMessage());
		}



		return false;
	}


	public void clearDB() throws DataBaseIOException {
		try {
			ForumLogger.getInstance().info("Clearing MySQL DB...", LoggerCategory.PERSISTANCE);
			Statement statement = con.createStatement();
			statement.addBatch("DELETE FROM SectionAdmins;");
			statement.addBatch("DELETE FROM Messages;");
			statement.addBatch("DELETE FROM Topics;");
			statement.addBatch("DELETE FROM Sections WHERE id != 0;");
			statement.addBatch("DELETE FROM Users WHERE id != 'admin';");
			statement.addBatch("DELETE FROM LoginStats;");
			statement.executeBatch();
			statement.close();
		} catch (SQLException e) {
			throw new DataBaseIOException("clearDB failed");
		}
	}


	@Override
	public AForumElement read(int elementID, ForumElementType type) throws WrongElementIDException, DataBaseIOException {
		try {
			Statement statement = con.createStatement();
			ResultSet resultSet;
			String sql = "";
			AForumElement element = null;

			switch(type) {
	   			case MESSAGE:
	   				sql = "SELECT * FROM Messages WHERE id = '" +	elementID + "';";
	   				resultSet = statement.executeQuery(sql);
	   				if(resultSet.next() != false) {
	   					element = new Message(resultSet.getString("subject"), resultSet.getString("author"), resultSet.getString("body"));
	   					element.setElementID(elementID);
	   					element.setParentID(resultSet.getInt("pid"));
	   					element.setTimeStamp(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(resultSet.getTimestamp("time")));
	   				} else {
	   					throw new WrongElementIDException("no such message: " + elementID);
	   				}
	   				resultSet.close();
					break;
	   			case TOPIC:
	   				sql = "SELECT * FROM Topics WHERE id = '" +	elementID + "';";
			   		resultSet = statement.executeQuery(sql);
			   		if(resultSet.next() != false) {
			   			element = new Topic();
	   					element.setElementID(elementID);
	   					element.setParentID(resultSet.getInt("pid"));
	   					element.setTimeStamp(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(resultSet.getTimestamp("time")));

	   					Statement sqlMessages = con.createStatement();
	   					ResultSet messages = sqlMessages.executeQuery("SELECT * FROM Messages WHERE pid = '" + elementID + "' ORDER BY id ASC;");

	   					while(messages.next() != false) {
	   						Message message = new Message(messages.getString("subject"), messages.getString("author"), messages.getString("body"));
	   						message.setElementID(messages.getInt("id"));
	   						message.setParentID(messages.getInt("pid"));
	   						message.setTimeStamp(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(messages.getTimestamp("time")));
	   						((Topic)element).addMessage(message);
	   					}

	   					messages.close();
	   					sqlMessages.close();
	   				} else {
	   					throw new WrongElementIDException("no such topic: " + elementID);
	   				}
			   		resultSet.close();
	   				break;
				case SECTION:
					sql = "SELECT * FROM Sections WHERE id = '" + elementID + "';";
			   		resultSet = statement.executeQuery(sql);
			   		if(resultSet.next() != false) {
	   					element = new Section(resultSet.getString("subject"), resultSet.getInt("pid"));
	   					element.setElementID(elementID);
	   					element.setTimeStamp(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(resultSet.getTimestamp("time")));
	   					// add sub sections headers
	   					Statement sqlSections = con.createStatement();
	   					ResultSet sections = sqlSections.executeQuery("SELECT * FROM Sections WHERE pid = '" + elementID + "' ORDER BY time ASC;");

	   					while(sections.next() != false) {
	   						SectionHeader sectionHeader = new SectionHeader(sections.getString("subject"), sections.getInt("pid"));
	   						sectionHeader.setElementID(sections.getInt("id"));
	   						sectionHeader.setTimeStamp(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sections.getTimestamp("time")));
	   						// add sub sections moderators
	   	   					Statement sqlSubAdmins = con.createStatement();
	   	   					ResultSet subAdmins = sqlSubAdmins.executeQuery("SELECT user_id FROM SectionAdmins WHERE section_id = '" + sections.getInt("id") + "';");

	   	   					while(subAdmins.next() != false) {
	   	   						sectionHeader.addSectionMod(subAdmins.getString("user_id"));
	   	   					}

	   	   					subAdmins.close();
	   	   					sqlSubAdmins.close();
	   						// add sub section newest topic
	   	   					Statement sqlTopic = con.createStatement();
	   	   					ResultSet topic = sqlTopic.executeQuery("SELECT * FROM Topics WHERE pid = '" + sections.getInt("id") + "' ORDER BY time DESC;");
	   	   					if(topic.next() != false) {
	   	   						TopicHeader topicHeader = new TopicHeader(topic.getString("subject"), topic.getInt("pid"));
	   	   						topicHeader.setElementID(topic.getInt("id"));
	   	   						topicHeader.setTimeStamp(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(topic.getTimestamp("time")));
	   	   						sectionHeader.setNewestTopic(topicHeader);
	   	   					}
	   	   					topic.close();
	   	   					sqlTopic.close();

	   						((Section)element).addSectionHeader(sectionHeader);
	   					}

	   					sections.close();
	   					sqlSections.close();
	   					// add section topics
	   					Statement sqlTopics = con.createStatement();
	   					ResultSet topics = sqlTopics.executeQuery("SELECT * FROM Topics WHERE pid = '" + elementID + "' ORDER BY time DESC;");

	   					while(topics.next() != false) {
	   						TopicHeader topicHeader = new TopicHeader(topics.getString("subject"), topics.getInt("pid"));
	   						topicHeader.setElementID(topics.getInt("id"));
	   						topicHeader.setTimeStamp(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(topics.getTimestamp("time")));

	   						// add topic header newest message
	   	   					Statement sqlMessage = con.createStatement();
	   	   					ResultSet message = sqlMessage.executeQuery("SELECT * FROM Messages WHERE pid = '" + topics.getInt("id") + "' ORDER BY id DESC;");
	   	   					if(message.next() != false) {
	   	   						MessageHeader messageHeader = new MessageHeader(message.getString("subject"), message.getInt("pid"));
	   	   						messageHeader.setElementID(message.getInt("id"));
	   	   						messageHeader.setTimeStamp(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(message.getTimestamp("time")));
	   	   						messageHeader.setUserName(message.getString("author"));
	   	   						topicHeader.setNewestMessage(messageHeader);
	   	   					}
	   						message.close();
	   						sqlMessage.close();

	   						((Section)element).addTopicHeader(topicHeader);
	   					}

	   					topics.close();
	   					sqlTopics.close();
	   					// add section moderators
	   					Statement sqlAdmins = con.createStatement();
	   					ResultSet admins = sqlAdmins.executeQuery("SELECT user_id FROM SectionAdmins WHERE section_id = '" + elementID + "';");

	   					while(admins.next() != false) {
	   						((Section)element).addSectionMod(admins.getString("user_id"));
	   					}

	   					admins.close();
	   					sqlAdmins.close();
	   				} else {
	   					throw new WrongElementIDException("no such section: " + elementID);
	   				}
			   		resultSet.close();
					break;
	   		}
	   		statement.close();
			return element;
		} catch (SQLException e) {
			throw new DataBaseIOException("read element failed");
		}
	}

	@Override
	public User read(String userName) throws WrongUserNameException, DataBaseIOException {
		try {
			User user = null;
			Statement statement = con.createStatement();

			String sql = "SELECT * FROM Users WHERE id = '" +	StringEscapeUtils.escapeSql(userName) + "';";

			ResultSet resultSet = statement.executeQuery(sql);
			if(resultSet.next() != false){
				user = new User(resultSet.getString("id"), resultSet.getString("password"), UserType.valueOf(resultSet.getString("type")));
				user.setSalt(resultSet.getInt("salt"));

				Statement sqlSections = con.createStatement();
				ResultSet sections = sqlSections.executeQuery("SELECT section_id FROM SectionAdmins WHERE user_id = '" + StringEscapeUtils.escapeSql(userName) + "';");

				while(sections.next() != false) {
					user.addSection(sections.getInt("section_id"));
				}

				sections.close();
				sqlSections.close();

			}
			else {
				throw new WrongUserNameException("no such user in DB: " + userName, userName);
			}

			resultSet.close();
			statement.close();

			return user;
		} catch (SQLException e) {
			throw new DataBaseIOException("read user failed");
		}
	}

	@Override
	public boolean write(AForumElement element) throws WrongElementIDException, DataBaseIOException {
	    try {
			Statement statement = con.createStatement();
			String sql;

			switch(element.elementIdetitity()) {
				case MESSAGE:

					Message message = (Message)element;

					sql = "INSERT INTO Messages (id, subject, author, body, pid) VALUES ('" +
					message.getElementID() + "', '" +
					StringEscapeUtils.escapeSql(message.getHeadLine()) + "', '" +
					StringEscapeUtils.escapeSql(message.getUserName()) + "', '" +
					StringEscapeUtils.escapeSql(message.getBody()) + "', '" +
					message.getParentID() + "') " +
					"ON DUPLICATE KEY UPDATE " +
					"subject = VALUES(subject), " +
					"body = VALUES(body), " +
					"pid = VALUES(pid);";

					statement.addBatch(sql);

					break;

				case TOPIC:

					Topic topic = (Topic)element;

					sql = "INSERT INTO Topics (id, subject, pid) VALUES ('" +
					topic.getElementID() + "', '" +
					StringEscapeUtils.escapeSql(topic.getHeadLine()) + "', '" +
					topic.getParentID() + "') " +
					"ON DUPLICATE KEY UPDATE " +
					"subject = VALUES(subject), " +
					"time = CURRENT_TIMESTAMP, " +
					"pid = VALUES(pid);";

					statement.addBatch(sql);

					break;

				case SECTION:

					Section section = (Section)element;

					sql = "INSERT INTO Sections (id, subject, pid) VALUES ('" +
					section.getElementID() + "', '" +
					StringEscapeUtils.escapeSql(section.getHeadLine()) + "', '" +
					section.getParentID() + "') " +
					"ON DUPLICATE KEY UPDATE " +
					"subject = VALUES(subject), " +
					"pid = VALUES(pid);";

					statement.addBatch(sql);

					sql = "DELETE FROM SectionAdmins WHERE section_id = '" + section.getElementID() + "';";
					statement.addBatch(sql);

					for(String user : section.getAllSectionMods()) {
						sql = "INSERT IGNORE INTO SectionAdmins (section_id,user_id) VALUES ('" +
						section.getElementID() + "', '" +
						StringEscapeUtils.escapeSql(user) +  "');";
						statement.addBatch(sql);
					}

					break;
			}
			statement.addBatch("UPDATE GeneralId SET general_id = '" + DataHandler.elementGeneralID + "';");
			statement.executeBatch();
			statement.close();
			return true;
		} catch (BatchUpdateException wrongIdException) {
			throw new WrongElementIDException("invalid element parameters");
		} catch (Exception generalException) {
			throw new DataBaseIOException("bad access to DB: " + generalException.getMessage());
		}
	}


	@Override
	public boolean write(User user) throws DataBaseIOException {
		try {
			Statement statement = con.createStatement();

			String sql = "INSERT INTO Users (id, password, salt, type) VALUES ('" +
				StringEscapeUtils.escapeSql(user.getUserName()) + "', '" +
				user.getPassword() + "', '" +
				user.getUserSalt() + "', '" +
				user.getUserType() + "') " +
				"ON DUPLICATE KEY UPDATE " +
				"type = VALUES(type);";
			statement.addBatch(sql);

			// if password needs to be changed
			if(user.getPassword() != null) {
				sql = "UPDATE Users " +
						"SET password = '" + user.getPassword() + "' " +
						"WHERE id = '" + StringEscapeUtils.escapeSql(user.getUserName()) + "';";
				statement.addBatch(sql);
			}

			sql = "DELETE FROM SectionAdmins WHERE user_id = '" + StringEscapeUtils.escapeSql(user.getUserName()) + "';";
			statement.addBatch(sql);

			for(int sectionID : user.getAllSections()) {
				sql = "INSERT IGNORE INTO SectionAdmins (section_id,user_id) VALUES ('" +
				sectionID + "', '" +
				StringEscapeUtils.escapeSql(user.getUserName()) +  "');";
				statement.addBatch(sql);
			}

			statement.executeBatch();
			statement.close();
			return true;

		} catch (SQLException e) {
			throw new DataBaseIOException("write failed");
		}
	}


	@Override
	public boolean delete(String userName) throws DataBaseIOException {
		try {
			Statement statement = con.createStatement();

			String sql = "DELETE FROM Users WHERE id = '" +	StringEscapeUtils.escapeSql(userName) + "';";

			statement.execute(sql);
			statement.close();

			return true;
		} catch (SQLException e) {
			throw new DataBaseIOException("delete user failed");
		}
	}

	@Override
	public boolean delete(int elementID, ForumElementType type) throws DataBaseIOException {
		try {
			Statement statement = con.createStatement();
			String sql="";

			switch(type) {
				case MESSAGE:
					sql = "DELETE FROM Messages WHERE id = '" +	elementID + "';";
					break;
				case TOPIC:
					sql = "DELETE FROM Topics WHERE id = '" +	elementID + "';";
					break;
				case SECTION:
					sql = "DELETE FROM Sections WHERE id = '" +	elementID + "';";
					break;
			}

	   		statement.execute(sql);
	   		statement.close();

	   		return true;
		} catch (SQLException e) {
			throw new DataBaseIOException("delete element failed");
		}
	}


	@Override
	public void setLoginTime(String strUserName) throws DataBaseIOException {
		try {
			Statement statement = con.createStatement();

			String sql = "SELECT * FROM LoginStats WHERE user_id = '" +
							StringEscapeUtils.escapeSql(strUserName) +
							"' AND logout_time = 0;";

			ResultSet resultSet = statement.executeQuery(sql);
			if(resultSet.next() == false){
				sql = "INSERT INTO LoginStats (user_id) VALUES ('" +
						StringEscapeUtils.escapeSql(strUserName) + "')";
				Statement stats = con.createStatement();
				stats.execute(sql);
				stats.close();
			}
			resultSet.close();
	   		statement.close();
		} catch (SQLException e) {
			throw new DataBaseIOException("set login time failed");
		}
	}

	@Override
	public void setLogoutTime(String username) throws DataBaseIOException {
		try {
			Statement statement = con.createStatement();

			String sql = "UPDATE LoginStats " +
							"SET logout_time = CURRENT_TIMESTAMP " +
							"WHERE user_id = '" + StringEscapeUtils.escapeSql(username) + "' " +
							"AND logout_time = 0;";

			statement.execute(sql);
			statement.close();
		} catch (Exception e) {
			throw new DataBaseIOException("set logout time failed");
		}
	}



	@Override
	public double[] getMessageCountStatistics(Date date) throws DataBaseIOException {
		try {
			double[] result = new double[24];
			for(int i = 0; i < result.length; i++) {
				result[i] = 0;
			}

			Statement statement = con.createStatement();

			String sql = "SELECT HOUR(time) AS Hour, COUNT(id) AS Count FROM Messages " +
							"WHERE TO_DAYS(time) = TO_DAYS('" +
							new SimpleDateFormat("yyyy-MM-dd").format(date)  +
							"') GROUP BY Hour;";

			ResultSet resultSet = statement.executeQuery(sql);

			while(resultSet.next() != false){
				result[resultSet.getInt("Hour")] = resultSet.getInt("Count");
			}

			return result;
		} catch (Exception e) {
			throw new DataBaseIOException("get message count statistics failed");
		}
	}

	@Override
	public double[] getUserCountStatistics(Date date) throws DataBaseIOException {
		try {
			double[] result = new double[24];
			for(int i = 0; i < result.length; i++) {
				result[i] = 0;
			}

			Statement statement = con.createStatement();

			String day = new SimpleDateFormat("yyyy-MM-dd").format(date);
			String sql = "SELECT TIMESTAMPDIFF(HOUR, '" + day + "' , login_time) AS Start, " +
							"TIMESTAMPDIFF(HOUR, '" + day + "' , logout_time) + 1 AS End FROM LoginStats " +
							"WHERE TO_DAYS(login_time) <= TO_DAYS('" + day + "') " +
							"AND (logout_time = 0 OR TO_DAYS(logout_time) >= TO_DAYS('" + day +	"'));";

			ResultSet resultSet = statement.executeQuery(sql);

			// if query is for today, set upper bound to next hour
			boolean today = false;
			int hour = -1;
			if(day.equals(new SimpleDateFormat("yyyy-MM-dd").format(new Date()))) {
				today = true;
				hour = Calendar.getInstance().get(Calendar.HOUR_OF_DAY) + 1;
			}

			while(resultSet.next() != false){
				int start = Math.max(resultSet.getInt("Start"), 0);
				int end = resultSet.getInt("End") == 0 ? (today ? hour : 24) : Math.min(resultSet.getInt("End"), 24);

				for(int i = start; i < end; i++) {
					result[i]++;
				}
			}

			return result;
		} catch (SQLException e) {
			throw new DataBaseIOException("get user count statistics failed");
		}
	}

	@Override
	public double[] getUserMessagePerDay(Date fromDate, String userName) throws DataBaseIOException{
		try {
			double[] result = new double[31];
			for(int i = 0; i < result.length; i++) {
				result[i] = 0;
			}

			Statement statement = con.createStatement();

			String day = new SimpleDateFormat("yyyy-MM-dd").format(fromDate);
			String sql = "SELECT DATEDIFF(time, '" + day + "') AS Day, COUNT(id) AS Count FROM Messages " +
							"WHERE author = '" + StringEscapeUtils.escapeSql(userName) + "' " +
							"AND DATEDIFF(time, '" + day + "') BETWEEN 0 AND 30 " +
							"GROUP BY Day;";

			ResultSet resultSet = statement.executeQuery(sql);

			while(resultSet.next() != false){
				result[resultSet.getInt("Day")] = resultSet.getInt("Count");
			}

			return result;
		} catch (SQLException e) {
			throw new DataBaseIOException("get user message per day failed");
		}
	}

	@Override
	public Vector<String> getUsernameList() throws DataBaseIOException {
		try {
			Vector<String> result = new Vector<String>();

			Statement statement = con.createStatement();
			String sql = "SELECT id FROM Users ORDER BY id ASC;";
			ResultSet resultSet = statement.executeQuery(sql);

			while(resultSet.next() != false){
				result.add(resultSet.getString("id"));
			}

			return result;
		} catch (SQLException e) {
			throw new DataBaseIOException("get usernames list failed");
		}
	}


/*	@Override
	public boolean getLock(String lock) throws Exception {
		boolean result = false;

		Statement statement = con.createStatement();
		String sql = "SELECT GET_LOCK('" + StringEscapeUtils.escapeSql(lock) + "', 60);";
		ResultSet resultSet = statement.executeQuery(sql);

		if(resultSet.next() != false){
			result = resultSet.getInt(1) == 1;
		}

		resultSet.close();
		statement.close();
		return result;
	}

	@Override
	public void releaseLock(String lock) throws Exception {
		Statement statement = con.createStatement();

		String sql = "SELECT RELEASE_LOCK('" + StringEscapeUtils.escapeSql(lock) + "');";
		statement.execute(sql);
		statement.close();
	}
*/
}
